using System;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.IDAL;
using OgilvyOne.Model;
using System.Web;

namespace OgilvyOne.SqlServerDAL
{
    public class MenuDesc : IMenuDesc
    {
        // Fields
        private const string SQL_Menu_Info_SAVE = "\r\n\r\n\t\tUPDATE [dbo].[Menu_Info] SET\r\n\t\t[Menu_Desc] = @Menu_Desc,\r\n        [menu_title]=@menu_title,\r\n\t\t[Pic_Asset_ID] = @Pic_Asset_ID,\r\n\t\t[Flash_Asset_ID] = @Flash_Asset_ID,\r\n\t\t[Desc_Tem_ID] = @Desc_Tem_ID,\r\n\t\t[State] = @State,\r\n        [IS_HOTSPOT]=@hotspot,\r\n        [Menu_title_image]=@menu_title_image,\r\n        [Menu_pic_image]=@menu_pic_image,\r\n        [Menu_content]=@menu_content,\r\n        [Pic_title_asset_id]=@pic_tilte_asset_id,\r\n        [InterChange_menu_id]=@insertchange_menuId\r\n\t\tWHERE\r\n\t\t[Menu_ID] = @Menu_ID\r\n\t\t";

        // Methods
        public MenuDescInfo GetCacheMenuDesc(int Menu_id)
        {
            string key = "menu_desc_" + Menu_id;
            MenuDescInfo menuDesc = HttpRuntime.Cache.Get(key) as MenuDescInfo;
            if (null == menuDesc)
            {
                menuDesc = this.GetMenuDesc(Menu_id);
                HttpRuntime.Cache.Insert(key, menuDesc, null, DateTime.Now.AddMinutes(2.0), TimeSpan.Zero);
            }
            return menuDesc;
        }

        public int getCnOrEnMenuId(int menu_id)
        {
            int cnOrEnRootId = new SysMenu().GetCnOrEnRootId(menu_id);
            string cmdText = string.Concat(new object[] { " select isnull(InterChange_menu_id,", cnOrEnRootId, ") from  menu_info where menu_id=", menu_id });
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, null))
            {
                while (reader.Read())
                {
                    cnOrEnRootId = reader.GetInt32(0);
                }
            }
            return cnOrEnRootId;
        }

        public MenuDescInfo GetMenuDesc(int Menu_id)
        {
            MenuDescInfo info = new MenuDescInfo();
            string cmdText = string.Format("select  u.[Menu_ID],u.[menu_title],u.[Menu_Desc],u.[Pic_Asset_ID],u.[Flash_Asset_ID],u.[Desc_Tem_ID],u.[State],u.[IS_HOTSPOT],u.[Menu_title_image],u.[Menu_pic_image],u.[Menu_content], u.[Pic_title_asset_id],u.[InterChange_menu_id],u.[MENU_ICON],u.[MENU_ICON1],u.[MENU_ICON2],u.[MENU_OTHER] FROM [dbo].[Menu_Info] u where u.[Menu_ID] = {0}", Menu_id);
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, null))
            {
                if (reader.Read())
                {
                    info = this.LoadFromDataReader(reader);
                }
            }
            return info;
        }

        public AssetInfo GetMenuDescAsset(int menuId, int flag)
        {
            MenuDescInfo cacheMenuDesc = this.GetCacheMenuDesc(menuId);
            if (null == cacheMenuDesc)
            {
                return null;
            }
            int picTitleAssetId = 0;
            if (flag == 1)
            {
                picTitleAssetId = cacheMenuDesc.Flash_asset_id;
            }
            else if (flag == 2)
            {
                picTitleAssetId = cacheMenuDesc.PicTitleAssetId;
            }
            else
            {
                picTitleAssetId = cacheMenuDesc.Pic_asset_id;
            }
            if (picTitleAssetId <= 0)
            {
                return null;
            }
            Asset asset = new Asset();
            return asset.getAssetInfo(picTitleAssetId);
        }

        public DataTable GetMenuDescAssetList(int menuId, int top, int hot)
        {
            return this.GetMenuDescAssetList(menuId, top, hot, false);
        }

        public DataTable GetMenuDescAssetList(int menuId, int top, int hot, bool isSelf)
        {
            string selectCommandText = "select ";
            if (top > 0)
            {
                object obj2 = selectCommandText;
                selectCommandText = string.Concat(new object[] { obj2, " top ", top, " " });
            }
            selectCommandText = selectCommandText + "mi.Menu_ID, mi.menu_title, mi.Menu_Desc, menu_content, mi.Pic_Asset_ID, mi.Flash_Asset_ID, mi.Desc_Tem_ID, \r\n                    mi.IS_HOTSPOT, (select asset_path from ASSET_INFO where asset_id = mi.Menu_title_image) as picpath, \r\n                    (select asset_path from ASSET_INFO where asset_id = mi.Menu_pic_image) as bigpicpath, \r\n                    (select asset_path from ASSET_INFO where asset_id = mi.Flash_Asset_ID) as flashpath, sm.menu_name, sm.menu_display_name, \r\n                    sm.Menu_out_url, sm.target FROM Menu_Info mi inner join SYS_MENU sm on mi.Menu_ID = sm.menu_uid where mi.state = 1 and sm.isdel = 0 ";
            if (isSelf)
            {
                selectCommandText = selectCommandText + " and sm.menu_uid = " + menuId;
            }
            else
            {
                selectCommandText = selectCommandText + " and sm.menu_parent_uid = " + menuId;
            }
            if (hot == 1)
            {
                selectCommandText = selectCommandText + " and mi.is_hotspot = 1 ";
            }
            selectCommandText = selectCommandText + " order by sm.menu_sord";
            DataTable dataTable = new DataTable();
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                new SqlDataAdapter(selectCommandText, connection).Fill(dataTable);
            }
            return dataTable;
        }

        public IList GetMenuDescList()
        {
            string cmdText = "SELECT u.[Menu_ID],u.[menu_title],u.[Menu_Desc],u.[Pic_Asset_ID],u.[Flash_Asset_ID],u.[Desc_Tem_ID],u.[State],u.[IS_HOTSPOT],u.[Menu_title_image],u.[Menu_pic_image],u.[Menu_content], u.[pic_title_asset_id],u.[InterChange_menu_id]\r\n\t    FROM   [dbo].[Menu_Info] u ";
            IList list = new ArrayList();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, null))
            {
                while (reader.Read())
                {
                    MenuDescInfo info = this.LoadFromDataReader(reader);
                    list.Add(info);
                }
            }
            return list;
        }

        public IList GetMenuDescList(int menuId)
        {
            string cmdText = "SELECT u.[Menu_ID],u.[menu_title],u.[Menu_Desc],u.[Pic_Asset_ID],u.[Flash_Asset_ID],u.[Desc_Tem_ID],u.[State],u.[IS_HOTSPOT],u.[Menu_title_image],u.[Menu_pic_image],u.[Menu_content], u.[pic_title_asset_id],u.[interchange_menu_id]\r\n\t    FROM   [dbo].[Menu_Info] u  where u.[Menu_ID] in ( select menu_uid from dbo.GetChildCategories(" + menuId + "))";
            IList list = new ArrayList();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, null))
            {
                while (reader.Read())
                {
                    MenuDescInfo info = this.LoadFromDataReader(reader);
                    list.Add(info);
                }
            }
            return list;
        }

        public bool insert(int menu_id, string menu_name)
        {
            bool flag = false;
            string cmdText = "insert into menu_info(menu_id,menu_title,Menu_Desc,Pic_Asset_ID,Flash_Asset_ID,Desc_Tem_ID,State,IS_HOTSPOT,Menu_title_image,Menu_pic_image,Menu_content,Pic_Title_Asset_ID,InterChange_menu_id)values(@menu_id,@menu_title,'',0,0,0,0,'0',0,0,'',0,0)";
            MenuDescInfo info = new MenuDescInfo();
            SqlConnection conn = null;
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@menu_id", SqlDbType.Int, 4), new SqlParameter("@menu_title", SqlDbType.VarChar, 200) };
            cmdParms[0].Value = menu_id;
            cmdParms[1].Value = menu_name;
            try
            {
                try
                {
                    conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC);
                    SQLHelper.ExecuteNonQuery(conn, CommandType.Text, cmdText, cmdParms);
                    flag = true;
                }
                catch (Exception exception)
                {
                    throw new Exception("MenuDesc", exception);
                }
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return flag;
        }

        private MenuDescInfo LoadFromDataReader(SqlDataReader reader)
        {
            MenuDescInfo info = new MenuDescInfo();
            if (!reader.IsDBNull(0))
            {
                info.Menu_id = reader.GetInt32(0);
            }
            if (!reader.IsDBNull(1))
            {
                info.Menu_title = reader.GetString(1);
            }
            if (!reader.IsDBNull(2))
            {
                info.Menu_desc = reader.GetString(2);
            }
            if (!reader.IsDBNull(3))
            {
                info.Pic_asset_id = reader.GetInt32(3);
            }
            if (!reader.IsDBNull(4))
            {
                info.Flash_asset_id = reader.GetInt32(4);
            }
            if (!reader.IsDBNull(5))
            {
                info.Desc_tem_id = reader.GetInt32(5);
            }
            if (!reader.IsDBNull(6))
            {
                info.State = reader.GetByte(6);
            }
            if (!reader.IsDBNull(7))
            {
                info.Hotspot = reader.GetString(7);
            }
            if (!reader.IsDBNull(8))
            {
                info.Menu_title_image = reader.GetInt32(8);
            }
            if (!reader.IsDBNull(9))
            {
                info.Menu_pic_image = reader.GetInt32(9);
            }
            if (!reader.IsDBNull(10))
            {
                info.Menu_content = reader.GetString(10);
            }
            if (!reader.IsDBNull(11))
            {
                info.PicTitleAssetId = reader.GetInt32(11);
            }
            if (!reader.IsDBNull(12))
            {
                info.Insertchange_Menu_id = reader.GetInt32(12);
            }
            try
            {
                info.MENU_ICON = reader.GetString(13);
                info.MENU_ICON1 = reader.GetString(14);
                info.MENU_ICON2 = reader.GetString(15);
                info.MENU_OTHER = reader.GetString(16);
            }
            catch { }
            return info;
        }

        public int UpdateMenuDesc(MenuDescInfo mMenuDesc)
        {
            SqlConnection conn = null;
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Menu_ID", SqlDbType.Int), new SqlParameter("@menu_title", SqlDbType.VarChar, 200), new SqlParameter("@Menu_Desc", SqlDbType.Text, 0x7fffffff), new SqlParameter("@Pic_Asset_ID", SqlDbType.Int), new SqlParameter("@Flash_Asset_ID", SqlDbType.Int), new SqlParameter("@Desc_Tem_ID", SqlDbType.Int), new SqlParameter("@State", SqlDbType.TinyInt), new SqlParameter("@hotspot", SqlDbType.VarChar, 1), new SqlParameter("@menu_title_image", SqlDbType.Int, 4), new SqlParameter("@menu_pic_image", SqlDbType.Int, 4), new SqlParameter("@menu_content", SqlDbType.Text), new SqlParameter("@pic_tilte_asset_id", SqlDbType.Int, 4), new SqlParameter("@insertchange_menuId", SqlDbType.Int, 4) };
            cmdParms[0].Value = mMenuDesc.Menu_id;
            cmdParms[1].Value = mMenuDesc.Menu_title;
            cmdParms[2].Value = mMenuDesc.Menu_desc;
            cmdParms[3].Value = mMenuDesc.Pic_asset_id;
            cmdParms[4].Value = mMenuDesc.Flash_asset_id;
            cmdParms[5].Value = mMenuDesc.Desc_tem_id;
            cmdParms[6].Value = mMenuDesc.State;
            cmdParms[7].Value = mMenuDesc.Hotspot;
            cmdParms[8].Value = mMenuDesc.Menu_title_image;
            cmdParms[9].Value = mMenuDesc.Menu_pic_image;
            cmdParms[10].Value = mMenuDesc.Menu_content;
            cmdParms[11].Value = mMenuDesc.Pic_title_asset_id;
            cmdParms[12].Value = mMenuDesc.Insertchange_Menu_id;
            try
            {
                try
                {
                    conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC);
                    SQLHelper.ExecuteNonQuery(conn, CommandType.Text, "\r\n\r\n\t\tUPDATE [dbo].[Menu_Info] SET\r\n\t\t[Menu_Desc] = @Menu_Desc,\r\n        [menu_title]=@menu_title,\r\n\t\t[Pic_Asset_ID] = @Pic_Asset_ID,\r\n\t\t[Flash_Asset_ID] = @Flash_Asset_ID,\r\n\t\t[Desc_Tem_ID] = @Desc_Tem_ID,\r\n\t\t[State] = @State,\r\n        [IS_HOTSPOT]=@hotspot,\r\n        [Menu_title_image]=@menu_title_image,\r\n        [Menu_pic_image]=@menu_pic_image,\r\n        [Menu_content]=@menu_content,\r\n        [Pic_title_asset_id]=@pic_tilte_asset_id,\r\n        [InterChange_menu_id]=@insertchange_menuId\r\n\t\tWHERE\r\n\t\t[Menu_ID] = @Menu_ID\r\n\t\t", cmdParms);
                }
                catch (Exception exception)
                {
                    throw new Exception("MenuDesc", exception);
                }
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return 0;
        }

        public int UpdateMenuOtherDesc(int menu_id, int insertchange_menuId)
        {
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@menu_id", SqlDbType.Int, 4), new SqlParameter("@insertchange_menuId", SqlDbType.Int, 4) };
            SqlConnection conn = null;
            cmdParms[0].Value = menu_id;
            cmdParms[1].Value = insertchange_menuId;
            string cmdText = "update menu_info set InterChange_menu_id=@menu_id where menu_id=@insertchange_menuId";
            try
            {
                try
                {
                    conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC);
                    SQLHelper.ExecuteNonQuery(conn, CommandType.Text, cmdText, cmdParms);
                }
                catch (Exception exception)
                {
                    throw new Exception("MenuDesc", exception);
                }
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return 0;
        }
    }
}